﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Globalization;

namespace TravelAgencyDAL
{
    public class DAO
    {
        private static DBaseDataContext cont;

        static DAO()
        {
            cont = new DBaseDataContext();
        }

        public static void AddDestination(string destinationName)
        {
            Destination dest = new Destination();
            dest.DestistionName = destinationName;
            cont.Destinations.InsertOnSubmit(dest);
            cont.SubmitChanges();
        }

        //Adding WayStation in a new Destination. The WayStation is added at the end
        //of the WayStation`s list.
        public static void AddWayStation(long settlementID, long destinationID, int distanceTo,
            int timeTo, int stay, decimal priceTo, int orderNumber)
        {
            WayStation wayStation = new WayStation();
            wayStation.SettlementID = settlementID;
            wayStation.DestinationID = destinationID;
            wayStation.WayStationDistanceTo = distanceTo;
            wayStation.WayStationTimeTo = timeTo;
            wayStation.WayStationStay = stay;
            wayStation.WayStationPriceTo = priceTo;
            wayStation.WayStationOrderNumber = orderNumber;
            cont.WayStations.InsertOnSubmit(wayStation);
            cont.SubmitChanges();
        }

        //Inserting new WayStation in existing Destination. Assigning new values for the WayStation before
        //which the new WayStation is added(newDistanceTo, newTimeTo, newStay, newPriceTo). 
        public static void InsertWayStation(long settlementID, long destinationID, int distanceTo,
            int timeTo, int stay, decimal priceTo, long addAfterWayStationID,
             int newDistanceTo, int newTimeTo, decimal newPriceTo)
        {
            int orderNumber = GetWayStationByID(addAfterWayStationID).WayStationOrderNumber;
            List<WayStation> wayList = GetAllWayStationsToEnd(orderNumber, destinationID);
            WayStation newWayStation = new WayStation();

            newWayStation.SettlementID = settlementID;
            newWayStation.DestinationID = destinationID;
            newWayStation.WayStationDistanceTo = distanceTo;
            newWayStation.WayStationTimeTo = timeTo;
            newWayStation.WayStationStay = stay;
            newWayStation.WayStationPriceTo = priceTo;
            newWayStation.WayStationOrderNumber = (++orderNumber);
            cont.WayStations.InsertOnSubmit(newWayStation);

            foreach (WayStation way in wayList)
            {
                way.WayStationOrderNumber++;
            }

            wayList.First().WayStationDistanceTo = newDistanceTo;
            wayList.First().WayStationTimeTo = newTimeTo;
            wayList.First().WayStationPriceTo = newPriceTo;

            cont.SubmitChanges();
        }

        public static void AddSettlement(string name)
        {
            Settlement settlement = new Settlement();
            settlement.SettlementName = name;
            cont.Settlements.InsertOnSubmit(settlement);
            cont.SubmitChanges();
        }

        public static void AddStartHour(StartHour startHour)
        {
            cont.StartHours.InsertOnSubmit(startHour);
            cont.SubmitChanges();
        }

        public static string GetWeekSchedule(long destID)
        {
            var weekSched = (from start in cont.StartHours
                             join schedule in cont.SheduleTypes
                             on start.SheduleTypeID equals schedule.SheduleTypeID
                             where start.DestinationID == destID
                             select schedule.SheduleDayOfWeek);
                                
           
            if(weekSched.Count()>0)
            {
                string weekSchedule = weekSched.First();
                return weekSchedule;
            }
            else
                return "";
           
        }

        public static List<int> GetAllStartHoursForDestination(long destID)
        {
            var satrtHours = (from s in cont.StartHours
                              join d in cont.Destinations
                              on s.DestinationID equals d.DestinationID
                              where destID == d.DestinationID
                              select s.StartHourValue).Distinct();

            return satrtHours.ToList<int>();
        }

        private static List<WayStation> GetAllWayStationsToEnd(int orderNumber, long destinationID)
        {
            var allToEnd = from w in cont.WayStations
                           join d in cont.Destinations
                           on w.DestinationID equals d.DestinationID
                           where w.WayStationOrderNumber > orderNumber & d.DestinationID == destinationID
                           orderby w.WayStationOrderNumber
                           select w;

            return allToEnd.ToList();
        }

        public static List<string> GetWayStationsToEnd(int orderNumber, long destinationID)
        {
            var allToEnd = from w in cont.WayStations
                           join d in cont.Destinations
                           on w.DestinationID equals d.DestinationID
                           where w.WayStationOrderNumber > orderNumber && d.DestinationID == destinationID
                           orderby w.WayStationOrderNumber
                           select w.Settlement.SettlementName;

            return allToEnd.ToList();
        }

        public static WayStation GetTheLastWayStationForDestination(long destID)
        {
            var allToEnd = from w in cont.WayStations
                           join d in cont.Destinations
                           on w.DestinationID equals d.DestinationID
                           where d.DestinationID == destID
                           orderby w.WayStationOrderNumber descending
                           select w;

            return allToEnd.First();
        }

        public static List<WayStation> GetAllWayStationForDestination(long destinationID)
        {
            var allWayStation = from w in cont.WayStations
                                join d in cont.Destinations
                                on w.DestinationID equals d.DestinationID
                                where w.DestinationID == destinationID
                                orderby w.WayStationOrderNumber
                                select w;

            return allWayStation.ToList();
        }

        public static void DeleteDestination(long destinationID)
        {
            var destination = (from d in cont.Destinations
                               where d.DestinationID == destinationID
                               select d).First();

            cont.Destinations.DeleteOnSubmit(destination);
            cont.SubmitChanges();
        }

        public static void DeleteBus(long busID)
        {
            var bus = (from b in cont.Buses
                       where b.BusID == busID
                       select b).First();

            cont.Buses.DeleteOnSubmit(bus);
            cont.SubmitChanges();
        }

        //Deleating a WayStation. Assigning new values for the WayStation before
        //which the WayStation is deleated(newDistanceTo, newTimeTo, newStay, newPriceTo).
        public static void DeleteWayStation(long wayStationID,
            int newDistanceTo, int newTimeTo, int newStay, decimal newPriceTo)
        {
            long destinationID = GetWayStationByID(wayStationID).DestinationID;
            int orderNumber = GetWayStationByID(wayStationID).WayStationOrderNumber;

            List<WayStation> wayList = GetAllWayStationsToEnd(orderNumber, destinationID);

            foreach (WayStation way in wayList)
            {
                way.WayStationOrderNumber--;
            }

            wayList.First().WayStationDistanceTo = newDistanceTo;
            wayList.First().WayStationTimeTo = newTimeTo;
            wayList.First().WayStationStay = newStay;
            wayList.First().WayStationPriceTo = newPriceTo;

            cont.WayStations.DeleteOnSubmit(GetWayStationByID(wayStationID));
            cont.SubmitChanges();
        }

        private static WayStation GetWayStationByID(long wayStationID)
        {
            var wayStation = (from w in cont.WayStations
                              where w.WayStationID == wayStationID
                              select w).First();

            return wayStation;
        }

        public static List<Settlement> GetAllSettlements()
        {
            var allSettlements = from settlements in cont.Settlements
                                 select settlements;

            return allSettlements.ToList<Settlement>();
        }

        public static List<Destination> GetAllDestinations()
        {
            var getDestinations = from dest in cont.Destinations
                                  select dest;

            return getDestinations.ToList();
        }

        public static void AddScheduleType(string schedule)
        {
            SheduleType scheduleType = new SheduleType();
            scheduleType.SheduleDayOfWeek = schedule;
            cont.SheduleTypes.InsertOnSubmit(scheduleType);
            cont.SubmitChanges();
        }

        public static long GetScheduleTypeID(string schedule)
        {
            var result = from s in cont.SheduleTypes
                         where s.SheduleDayOfWeek == schedule
                         select s.SheduleTypeID;

            return result.First();
        }

        public static long GetSettlementID(string settlementName)
        {
            var result = from s in cont.Settlements
                         where s.SettlementName == settlementName
                         select s.SettlementID;

            return result.First();
        }

        public static void AddNewBus(Buse bus)
        {
            cont.Buses.InsertOnSubmit(bus);
            cont.SubmitChanges();
        }

        public static List<Buse> GetAllBuses()
        {
            var buses = from b in cont.Buses
                        select b;

            return buses.ToList();
        }

        public static long GetWeekScheduleForDestination(long destID)
        {
            var schedule = from d in cont.Destinations
                           join s in cont.StartHours
                           on d.DestinationID equals s.DestinationID
                           where s.DestinationID == destID
                           select s.SheduleTypeID;

            return schedule.First();
        }

        public static void AttachBus(BusShedule busSchedule)
        {
            cont.BusShedules.InsertOnSubmit(busSchedule);
            cont.SubmitChanges();
        }

        public static void DetachBus(string date, string startHour, string busNum, long destID)
        {
            //BusShedule busSchedule = new BusShedule();
            int startHourValue = GetStartHourValue(startHour);
            DateTime dateTime = GetTheDate(date);

            var startHourID = (from s in cont.StartHours
                               where ((s.StartHourValue == startHourValue) && (s.DestinationID == destID))
                               select s.StartHourID).First();

            var busNumID = (from b in cont.Buses
                            where b.BusRegNumber == busNum
                            select b.BusID).First();

            

            cont.BusShedules.DeleteOnSubmit(RemoveBus(busNumID, dateTime, startHourID));
            cont.SubmitChanges();
        }

        private static BusShedule RemoveBus(int busID, DateTime date, long sHour)
        {
            var busSchedule = from b in cont.BusShedules
                              where ((b.BusID == busID) && (b.BusSheduleDate == date) && (b.StartHourID == sHour))
                              select b;

            return busSchedule.First();
        }

        private static DateTime GetTheDate(string date)
        {
            DateTime dateTime = new DateTime();
            string[] splittedDate = date.Split(new char[] { '/' }, StringSplitOptions.RemoveEmptyEntries);
            
            DateTime currentDateTime = dateTime.AddYears(Convert.ToInt32(splittedDate[2]) - 1);
            currentDateTime = currentDateTime.AddMonths(Convert.ToInt32(splittedDate[0]) - 1);
            currentDateTime = currentDateTime.AddDays(Convert.ToInt32(splittedDate[1]) - 1);

            return currentDateTime;
        }

        public static List<StartHour> GetAllStartHoursForDest(long destID)
        {
            var startHours = from s in cont.StartHours
                             where s.DestinationID == destID
                             select s;

            return startHours.ToList();
        }

        public static int GetWayStationTimesFromBeginning(long destID, int orderNumber)
        {
            int time = 0;
            int stay = 0;

            var allTimesFromBeginning = from w in cont.WayStations
                                        join d in cont.Destinations
                                        on w.DestinationID equals d.DestinationID
                                        where w.WayStationOrderNumber <= orderNumber && d.DestinationID == destID
                                        select w;

            var allStaysFromBeginning = from w in cont.WayStations
                                        join d in cont.Destinations
                                        on w.DestinationID equals d.DestinationID
                                        where w.WayStationOrderNumber < orderNumber && d.DestinationID == destID
                                        select w;

            foreach (var t in allTimesFromBeginning)
            {
                time += t.WayStationTimeTo;
            }

            foreach (var s in allStaysFromBeginning)
            {
                stay += s.WayStationStay;
            }

            return time + stay;
        }

        public static int GetTheTimeForDestination(long destID)
        {
            int time;

            var times = from w in cont.WayStations
                        where w.DestinationID == destID
                        select w;

            foreach (var t in times)
            {
                time += t.WayStationTimeTo + t.WayStationStay;
            }

            return time;
        }

        public static int GetTimeBetweenConcreteStations(long destID, int orderNumberFrom, int orderNumberTo)
        {
            int time = 0;
            int stay = 0;

            var allTimes = from w in cont.WayStations
                           join d in cont.Destinations
                           on w.DestinationID equals d.DestinationID
                           where w.WayStationOrderNumber > orderNumberFrom && w.WayStationOrderNumber <= orderNumberTo
                               && d.DestinationID == destID
                           select w;

            var allStays = from w in cont.WayStations
                           join d in cont.Destinations
                           on w.DestinationID equals d.DestinationID
                           where w.WayStationOrderNumber > orderNumberFrom && w.WayStationOrderNumber < orderNumberTo
                               && d.DestinationID == destID
                           select w;

            foreach (var t in allTimes)
            {
                time += t.WayStationTimeTo;
            }

            foreach (var s in allStays)
            {
                stay += s.WayStationStay;
            }

            return time + stay;
        }

        public static int GetDistancesBetweenConcreteStations(long destID, int orderNumberFrom, int orderNumberTo)
        {
            int distance = 0;

            var allDistances = from w in cont.WayStations
                               join d in cont.Destinations
                               on w.DestinationID equals d.DestinationID
                               where w.WayStationOrderNumber > orderNumberFrom && w.WayStationOrderNumber <= orderNumberTo
                                   && d.DestinationID == destID
                               select w;

            foreach (var d in allDistances)
            {
                distance += d.WayStationDistanceTo;
            }

            return distance;
        }

        public static decimal GetPricesBetweenConcreteStations(long destID, int orderNumberFrom, int orderNumberTo)
        {
            decimal price = 0;

            var allPrices = from w in cont.WayStations
                            join d in cont.Destinations
                            on w.DestinationID equals d.DestinationID
                            where w.WayStationOrderNumber > orderNumberFrom && w.WayStationOrderNumber <= orderNumberTo
                                && d.DestinationID == destID
                            select w;

            foreach (var p in allPrices)
            {
                price += p.WayStationPriceTo;
            }

            return price;
        }

        public static void RemoveStartHour(string startHour)
        {
            int result = GetStartHourValue(startHour);

            var shID = from s in cont.StartHours
                       where s.StartHourValue == result
                       select s;

            cont.StartHours.DeleteOnSubmit(shID.First());
            cont.SubmitChanges();
        }

        public static bool DoesStartHourExists(string startHour)
        {
            int result = GetStartHourValue(startHour);

            try
            {
                var sh = (from s in cont.StartHours
                          where s.StartHourValue == result
                          select s).First();

                return true;
            }
            catch (InvalidOperationException)
            {
                return false;
            }
        }

        private static int GetStartHourValue(string startHour)
        {
            string[] time = startHour.Split(new char[] { ':' }, StringSplitOptions.RemoveEmptyEntries);
            return (Convert.ToInt32(time[0]) * 60) + Convert.ToInt32(time[1]);
        }

        public static void AddUser(string firstName, string lastName, string eMail)
        {
            User newUser = new User();
            newUser.UserFirstName = firstName;
            newUser.UserLastName = lastName;
            newUser.UserEmail = eMail;
            cont.Users.InsertOnSubmit(newUser);
            Update();
        }

        public static List<string> GetBusesForStartHour(long destID, string startHour)
        {
            int startHourValue = GetStartHourValue(startHour);

            var buses = (from s in cont.StartHours
                         join bs in cont.BusShedules
                         on s.StartHourID equals bs.StartHourID
                         join b in cont.Buses
                         on bs.BusID equals b.BusID
                         where ((s.DestinationID == destID) && (s.StartHourValue == startHourValue))
                         select b.BusRegNumber).Distinct();

            return buses.ToList();
        }

        public static List<string> GetDatesForBus(long destID, string startHour, string busNum)
        {
            int startHourValue = GetStartHourValue(startHour);

            var buses = (from s in cont.StartHours
                         join bs in cont.BusShedules
                         on s.StartHourID equals bs.StartHourID
                         join b in cont.Buses
                         on bs.BusID equals b.BusID
                         where ((s.DestinationID == destID) && (s.StartHourValue == startHourValue)
                         && (b.BusRegNumber == busNum))
                         select bs.BusSheduleDate.ToShortDateString()).ToList();

            return buses;
        }

        public static string GetConcreteBusNumber(string startHour, DateTime date)
        {
            try
            {
                var busNum = (from n in cont.BusShedules
                              where n.BusSheduleDate == date && n.StartHour.StartHourValue ==
                              GetStartHourValue(startHour)
                              select n.Buse.BusRegNumber).First();

                return busNum;
            }
            catch (InvalidOperationException)
            {
                return "No bus attached!";
            }
        }

        public static bool DoesWayStationExists(long destID)
        {
            try
            {
                var wayStation = from w in cont.WayStations
                                 where w.DestinationID == destID
                                 select w;

                return true;
            }
            catch (InvalidOperationException)
            {
                return false;
            }
        }

        public static string[] GetDestinationWeekSchedule(long destID)
        {
            var weekSchedule = (from d in cont.SheduleTypes
                                join s in cont.StartHours
                                on d.SheduleTypeID equals s.SheduleTypeID
                                where s.DestinationID == destID
                                select d.SheduleDayOfWeek).First();

            return GetWeekScheduleList(weekSchedule);
        }

        private static string[] GetWeekScheduleList(string schedule)
        {
            if (schedule == "Every Day")
            {
                string[] destinationWeekSchedule = { "Monday", "Tuesday", "Wednesday", "Thursday",
                                                       "Friday", "Saturday", "Sunday" };
                return destinationWeekSchedule;
            }
            else if (schedule == "Saturday, Sunday")
            {
                string[] destinationWeekSchedule = { "Saturday, Sunday" };
                return destinationWeekSchedule;
            }
            else
            {
                string[] destinationWeekSchedule = schedule.Split(new char[] { ' ' },
                    StringSplitOptions.RemoveEmptyEntries);
                return destinationWeekSchedule;
            }
        }

        public static bool DoesDateExistsForThisHour(DateTime date, string startHour, long destID)
        {
            int startHourValue = GetStartHourValue(startHour);

            try
            {
                var check = (from s in cont.BusShedules
                            where ((s.StartHour.StartHourValue == startHourValue)
                                   && (s.BusSheduleDate == date) && (s.StartHour.DestinationID == destID))
                            select s.BusID).First();

                return true;
            }
            catch (InvalidOperationException)
            {
                return false;
            }
        }

        public static List<string> GetFreeBuses(long destID, DateTime date, string startHour, string wantedHour)
        {
            int startHourValue = GetStartHourValue(startHour);
            int wanted = GetStartHourValue(wantedHour);

            var buses = (from bs in cont.BusShedules
                         join start in cont.StartHours
                         on bs.StartHourID equals start.StartHourID
                         join d in cont.Destinations
                         on start.DestinationID equals d.DestinationID
                         join w in cont.WayStations
                         on d.DestinationID equals w.DestinationID
                         where bs.BusSheduleDate.Date == date
                             && d.DestinationID == destID
                             && start.StartHourValue == startHourValue
                         select bs.Buse.BusRegNumber).ToList();

            foreach (var bus in buses)
            {

            }
        }

        public static void Update()
        {
            cont.SubmitChanges();
        }

        #region Logon
        public static Employee Verification(string userName, string password)
        {
            var empl = from emp in cont.Employees
                       where (emp.UserName == userName & emp.Password == password)
                       select emp;
            if (empl.Count() > 0)
            {
                Employee current = empl.First<Employee>();
                return current;
            }
            else
                return null;
        }
        #endregion
    }
}
